Hierarchy

Configure hierarchies to enable drill down later on in Discover. These hierarchies are placed in a folder within the given dimension (red highlight below). When a level of the hierarchy is added to the query, the user can expand hierarchy levels, drill down, drill up, or drill to a specified level. Click here to learn more about drill functions.

Hierarchy Types

OLAP, Tabular, and SAP Hierarchies

When connecting to OLAP, Tabular, SAP HANA, and BW models, hierarchies in the data source are automatically displayed in Pyramid. When these hierarchies are added to the query, the hierarchy levels can be opened via the expand and collapse functions; this is known as a 'regular' hierarchy. As in the image below, the hierarchy's levels can be expanded and collapsed as needed, displaying multiple hierarchy levels simultaneously.

You can also right click on an element to drill into the 'drill path'; continue reading for details.

In this example, the query contains the Products hierarchy, and the Sales and Margin measures:

SQL Hierarchies

When building a data model from an SQL source, you have the options to build 'user hierarchies,' which will later enable drill down in queries. There are 2 options for building hierarchies: drill path, and regular.

Drill Path

When a 'drill path' hierarchy is added to the query in Discover, the query is updated according to the drill path. Each time you drill down, up, or to level, the query is updated to show only the members of the current level of hierarchy, for the chosen member.

In the image below, the query contains the Product hierarchy, and the Sales and Margin measures. As is seen in the breadcrumbs (green highlight), the query has been filtered by the elements on which the user drilled down: Accessories and Helmets. The resulting visual displays only the data points that were drilled into.

Users may expand down and collapse drill path hierarchies using the context menu functions. Unlike the expand and collapse icons exposed when working with regular hierarchies, the context menu expand down and collapse functions expand all member elements in the given level of the hierarchy, rather than a specified element:

Regular

This option produces the same hierarchy behavior as that of OLAP, Tabular, and SAP models.

When a 'regular' hierarchy is added to the grid, each element label contains an expand icon. The hierarchy levels are expanded and collapsed as needed, enabling you to show multiple hierarchy levels at once. You also have the option to use the drill path by right clicking on the relevant element (red highlight below).

Video

SQL Hierarchies

Build SQL hierarchies in Model and navigate them in Discover.

Build a Hierarchy

To build a hierarchy:

  1. From the Select Element panel, open the relevant table. Find the columns you want to include in the new hierarchy and drag them one at a time onto the New Hierarchy panel. Be sure to place the elements in the intended drill down order; toggle the arrows to change the order, or click the X to remove a column from the hierarchy (blue highlight below).
  2. From the New Hierarchy panel, select either Drill path or Regular.
  3. Give your hierarchy a name and description, confirm its display folder and related table, and assign a category to it if required.
  4. Click the Add button to confirm your hierarchy selection.
  5. The hierarchy will appear in the Existing hierarchies panel; to edit your hierarchy, click the edit icon, or click the X to delete it (green highlight).

Add as many hierarchies to your model as required.